[CODE GENERATION] How to generate DELETE statements in PL/SQL, based on the tables FK relations?

Posted by The chicken in the kitchen on Stack Overflow See other posts from Stack Overflow or by The chicken in the kitchen
Published on 2010-04-20T17:02:25Z Indexed on 2010/04/20 20:53 UTC
Read the original article Hit count: 261

Is it possible via script/tool to generate authomatically many delete statements based on the tables fk relations, using Oracle PL/SQL?

In example: I have the table: CHICKEN (CHICKEN_CODE NUMBER) and there are 30 tables with fk references to its CHICKEN_CODE that I need to delete; there are also other 150 tables foreign-key-linked to that 30 tables that I need to delete first.

Is there some tool/script PL/SQL that I can run in order to generate all the necessary delete statements based on the FK relations for me?

(by the way, I know about cascade delete on the relations, but please pay attention: I CAN'T USE IT IN MY PRODUCTION DATABASE, because it's dangerous!)

I'm using Oracle DataBase 10G R2.

This is the result I've written, but it is not recursive:

This is a view I have previously written, but of course it is not recursive!

CREATE OR REPLACE FORCE VIEW RUN
(
   OWNER_1,
   CONSTRAINT_NAME_1,
   TABLE_NAME_1,
   TABLE_NAME,
   VINCOLO
)
AS
     SELECT OWNER_1,
            CONSTRAINT_NAME_1,
            TABLE_NAME_1,
            TABLE_NAME,
            '('
            || LTRIM (
                  EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || COLUMN_NAME)),
                           '/x/text()'),
                  ',')
            || ')'
               VINCOLO
       FROM (  SELECT CON1.OWNER OWNER_1,
                      CON1.TABLE_NAME TABLE_NAME_1,
                      CON1.CONSTRAINT_NAME CONSTRAINT_NAME_1,
                      CON1.DELETE_RULE,
                      CON1.STATUS,
                      CON.TABLE_NAME,
                      CON.CONSTRAINT_NAME,
                      COL.POSITION,
                      COL.COLUMN_NAME
                 FROM DBA_CONSTRAINTS CON,
                      DBA_CONS_COLUMNS COL,
                      DBA_CONSTRAINTS CON1
                WHERE CON.OWNER = 'TABLE_OWNER' AND CON.TABLE_NAME = 'TABLE_OWNED'
                      AND ( (CON.CONSTRAINT_TYPE = 'P')
                           OR (CON.CONSTRAINT_TYPE = 'U'))
                      AND COL.TABLE_NAME = CON1.TABLE_NAME
                      AND COL.CONSTRAINT_NAME = CON1.CONSTRAINT_NAME
                      --AND CON1.OWNER = CON.OWNER
                      AND CON1.R_CONSTRAINT_NAME = CON.CONSTRAINT_NAME
                      AND CON1.CONSTRAINT_TYPE = 'R'
             GROUP BY CON1.OWNER,
                      CON1.TABLE_NAME,
                      CON1.CONSTRAINT_NAME,
                      CON1.DELETE_RULE,
                      CON1.STATUS,
                      CON.TABLE_NAME,
                      CON.CONSTRAINT_NAME,
                      COL.POSITION,
                      COL.COLUMN_NAME)
   GROUP BY OWNER_1,
            CONSTRAINT_NAME_1,
            TABLE_NAME_1,
            TABLE_NAME;

... and it contains the error of using DBA_CONSTRAINTS instead of ALL_CONSTRAINTS...

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about delete